package com.bytecodes.eaulas.conexion;

import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.sql.*;
import java.util.ArrayList;

import com.bytecodes.eaulas.beans.*;
import com.bytecodes.eaulas.comun.Log;
import com.bytecodes.eaulas.comun.servicios.TDSLanguageUtils;
import java.text.SimpleDateFormat;
import java.util.HashMap;

/**
 * Clase Gestora de Usuarios
 * 
 * @author Robert Artesona Torres
 * @version PAC4
 */

public class GestorConexion {
	private Connection conn = null;
        private enum TipoUsuario {CLIENTE, ADMINISTRADOR, TECNICO};
         private Log log = Log.getInstance();
	public GestorConexion(Connection conn ) {
		this.conn = conn;
	}

	/**
	 * Obtiene la conexion actual con la BD
	 * 
	 * @return la conexion actual con la Bd.
	 */
	public Connection getConnection() {
		return conn;
	}

	/**
	 * Devuelve una lista con los usuarios del sistema que cumplen el filtro pasado como par�metro
	 * 
	 * @param: filtro a aplicar a los usuarios del sistema
	 * @return: lista de usuarios que cumplen el filtro
	 */
	public List<Usuario> getTotal(String filtro) throws Exception {
                List<Usuario> listaUsuarios = new ArrayList<Usuario>();	
                Statement st = null;
                ResultSet rs = null;
                try {
                    st = conn.createStatement();
                    String select = "Select t1.userid,t2.name,t2.surname1,t2.surname2" +
                                    " from bookings t1" +
                                    " left join users t2 on (t1.userid = t2.userid)" +
                                    " Where t1.status = '" + filtro + "'" +
                                    " Group by t1.userid,t2.name,t2.surname1,t2.surname2" +
                                    " Order by t1.userid";                    
                    rs = st.executeQuery(select);
                    while (rs.next()) {
     			Usuario usuario = new Usuario();                        
                        usuario.setCodUsuario(rs.getString("userid"));
                        usuario.setNombre(rs.getString("name"));
                        usuario.setApellido1(rs.getString("surname1"));
                        usuario.setApellido2(rs.getString("surname2"));                                
                        listaUsuarios.add(usuario);
                    }
                }catch (SQLException e) {
                        e.printStackTrace();			
                }
                finally{
                        try{
                            rs.close();
                            st.close();
                            }catch(Exception e){
                                e.printStackTrace();
                            }
                        }
                return listaUsuarios;
	}	

	public List<Reserva> getReixa(String filtro, String client) throws Exception {
                List<Reserva> listaBooking = new ArrayList<Reserva>();	
                Statement st = null;
                ResultSet rs = null;
                try {
                    st = conn.createStatement();
                    
                    String select = "Select bookingid,userid,spaceid,start_time,end_time,pax,status,description_delete from bookings" +
                                    " Where status = '" + filtro + "'";
                    if (!client.equals("")) {
                        select = select + "   and userid = '" + client + "'";
                    }
                    select = select + " Order by bookingid";  
                    rs = st.executeQuery(select);
                    
                    while (rs.next()) {
       			Reserva booking = new Reserva();                        
                        booking.setIdReserva(rs.getInt("bookingid"));
                        booking.setIdUsuario(rs.getString("userid"));
                        booking.setIdAula(rs.getInt("spaceid"));
                        booking.setFechaPeticionInicioReserva(rs.getDate("start_time"));
                        booking.setFechaPeticionFinReserva(rs.getDate("end_time"));
                        booking.setNumeroOcupantesAula(rs.getInt("pax"));
                        booking.setStatusReserva(rs.getString("status"));
                        booking.setApunteReserva(rs.getString("description_delete"));
                        listaBooking.add(booking);
                    }
                }catch (SQLException e) {
                        e.printStackTrace();			
                }
                finally{
                        try{
                            rs.close();
                            st.close();
                            }catch(Exception e){
                                e.printStackTrace();
                            }
                        }
                return listaBooking;
	}	

	public String getDescripcio(Integer idAula) throws Exception {
                String spacedescription = "";
                Statement st = null;
                ResultSet rs = null;
                try {
                    st = conn.createStatement();
                    
                    String select = "Select spacedescription from spaces" +
                                    " Where spaceid = " + idAula;
                    rs = st.executeQuery(select);
                    
                    while (rs.next()) {
                        spacedescription = rs.getString("spacedescription");
                    }
                }catch (SQLException e) {
                        e.printStackTrace();			
                }
                finally{
                        try{
                            rs.close();
                            st.close();
                            }catch(Exception e){
                                e.printStackTrace();
                            }
                        }
                return spacedescription;
	}	

	public Double getImporte(Integer idBooking) throws Exception {
                Double importe = 0.0;
                Statement st = null;
                ResultSet rs = null;
                try {
                    st = conn.createStatement();
                    
                    String select = "Select total_price from payments" +
                                    " Where bookingid = " + idBooking;
                    rs = st.executeQuery(select);
                    
                    while (rs.next()) {
                        importe = rs.getDouble("total_price");
                    }
                }catch (SQLException e) {
                        e.printStackTrace();			
                }
                finally{
                        try{
                            rs.close();
                            st.close();
                            }catch(Exception e){
                                e.printStackTrace();
                            }
                        }
                return importe;
	}	
 
     public Boolean setUpdateResolt(String missatge, Integer Id) throws Exception {
        Boolean ok = false;
        Statement st = null;
        try {
            st = conn.createStatement();
            String update = "UPDATE bookings SET description_delete = '" + missatge +"' ," +
                            " status = '" + "Inactiva" + "'" + 
                            "WHERE bookingid = " + Id;
            st.executeUpdate(update);
            
            ok = true;
            }catch (SQLException e) {
                        e.printStackTrace();			
                }
                finally{
                        try{
                            conn.commit();
                            st.close();
                            }catch(Exception e){
                                e.printStackTrace();
                            }
                        }        
        return ok;
     }

     public Boolean setDeleteUser(Integer Id) throws Exception {
        Boolean ok = false;
        Statement st = null;
        try {
            st = conn.createStatement();
            String update = "UPDATE bookings SET status = '" + "Cancelada" + "'" + 
                            "WHERE bookingid = " + Id;
            st.executeUpdate(update);
            
            ok = true;
            }catch (SQLException e) {
                        e.printStackTrace();			
                }
                finally{
                        try{
                            conn.commit();
                            st.close();
                            }catch(Exception e){
                                e.printStackTrace();
                            }
                        }        
        return ok;
        
    }        
     
    public Usuario getClient(String idClient) throws Exception {
        Usuario usuario = new Usuario();            
        
        Statement st = null;
        ResultSet rs = null;
        try {
             st = conn.createStatement();
             String select = "Select password,name,surname1,surname2,roleid from users" +
                             " Where userid = '" + idClient + "'";
                    
             rs = st.executeQuery(select);
             while (rs.next()) {
                   usuario.setCodUsuario(idClient);
                   usuario.setPassword(rs.getString("password"));
                   usuario.setNombre(rs.getString("name"));
                   usuario.setApellido1(rs.getString("surname1"));
                   usuario.setApellido2(rs.getString("surname2")); 
                   usuario.setRolId(rs.getInt("roleid"));
                   usuario.setInactivationDate(rs.getDate("inactivation_date"));
             }
           }catch (SQLException e) {
                        e.printStackTrace();			
           }
           finally{
                   try{
                       rs.close();
                       st.close();
                      }catch(Exception e){
                                  e.printStackTrace();
                                         }
                   }
      return usuario;
    }
    
     
    public ArrayList getClientes() throws Exception {                    
        
        Statement st = null;
        ResultSet rs = null;
        ArrayList clientes = new ArrayList();
        try {
             st = conn.createStatement();
             String select = "Select * from users where roleid = 0 ";
                             
                    
             rs = st.executeQuery(select);
             while (rs.next()) {
                 Usuario usuario = new Usuario();  
                 usuario.setCodUsuario(rs.getString("userid"));
                   usuario.setPassword(rs.getString("password"));
                   usuario.setNombre(rs.getString("name"));
                   usuario.setApellido1(rs.getString("surname1"));
                   usuario.setApellido2(rs.getString("surname2")); 
                   usuario.setRolId(rs.getInt("roleid"));
                   clientes.add(usuario);
             }
           }catch (SQLException e) {
                        e.printStackTrace();			
           }
           finally{
                   try{
                       rs.close();
                       st.close();
                      }catch(Exception e){
                                  e.printStackTrace();
                                         }
                   }
      return clientes;
    }
    
    public List<HashMap> getDatosEstRend(String id, String fechaInicio, String fechaFin, String tipoEstadistica) throws Exception {                    
        
        String datoQuery = "";
        Statement st = null;
        ResultSet rs = null;  
        List<HashMap> datos = new ArrayList();
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy"); 
        
        if(tipoEstadistica.equals("cliente")){
            datoQuery = "bo.userid";
        }else if(tipoEstadistica.equals("aula")){
            datoQuery = "bo.spaceid";
        }
        
        try {
             st = conn.createStatement();
             String select = "";
               
             if(id != null && fechaInicio != null && fechaFin != null){
                
                 select = "select bo.*, pa.total_price from bookings bo, payments pa where "+datoQuery+" = "+
                     id + " and bo.bookingid = pa.bookingid  and bo.record_date > '"+dateFormat.parse(fechaInicio)+"' and bo.record_date < '"+dateFormat.parse(fechaFin)+"' order by "+datoQuery;
             
             }else if(id != null && fechaInicio == null && fechaFin == null){
                
                 select = "select bo.*, pa.total_price from bookings bo, payments pa where "+datoQuery+" = "+id+ " and bo.bookingid = pa.bookingid  order by "+datoQuery; 
             
             }else if(id != null && fechaInicio != null && fechaFin == null){
                
                 select = "select bo.*, pa.total_price from bookings bo, payments pa where "+datoQuery+" = "+
                     id + " and bo.bookingid = pa.bookingid and bo.record_date > '"+dateFormat.parse(fechaInicio)+"' order by "+datoQuery; 
             
             }else if(id != null && fechaInicio == null && fechaFin != null){
                
                 select = "select bo.*, pa.total_price from bookings bo, payments pa where "+datoQuery+" = "+
                     id + " and bo.bookingid = pa.bookingid and bo.record_date < '"+dateFormat.parse(fechaFin)+"' order by "+datoQuery; 
             
             }else if(id == null && fechaInicio != null && fechaFin == null){
                  
                select = "select bo.*, pa.total_price from bookings bo, payments pa, users us where bo.userid = us.userid and us.roleid = 0 and bo.bookingid = pa.bookingid  and "+
                        "bo.record_date > '"+dateFormat.parse(fechaInicio)+"' order by bo."+datoQuery; 
             
             }else if(id == null && fechaInicio != null && fechaFin != null){
               
                 select = "select bo.*, pa.total_price from bookings bo, payments pa, users us where bo.userid = us.userid and us.roleid = 0 and "+
                         "bo.bookingid = pa.bookingid and bo.record_date > '"+dateFormat.parse(fechaInicio)+
                         "' and bo.record_date < '"+dateFormat.parse(fechaFin)+"' order by bo."+datoQuery; 
             
             }else if(id == null && fechaInicio == null && fechaFin != null){
               
                 select = "select bo.*, pa.total_price from bookings bo, payments pa, users us where bo.userid = us.userid and us.roleid = 0 and "+
                         "bo.bookingid = pa.bookingid"+
                         " and bo.record_date < '"+dateFormat.parse(fechaFin)+"' order by bo."+datoQuery; 
             
             }else if(id == null && fechaInicio == null && fechaFin == null){
                
                 select = "select bo.*, pa.total_price from bookings bo, payments pa, users us where bo.userid = us.userid and us.roleid = 0 and bo.bookingid = pa.bookingid"+
                         " order by bo."+datoQuery; 
             }              
                 System.out.println(select);   
             rs = st.executeQuery(select);
             while (rs.next()) {
                 HashMap usuario = new HashMap();  
                 usuario.put("userid", rs.getString("userid"));
                 usuario.put("spaceid", rs.getInt("spaceid"));
                 usuario.put("personas", rs.getInt("pax"));                 
                 usuario.put("status", rs.getString("status"));
                 usuario.put("total", rs.getDouble("total_price"));
                   datos.add(usuario);
             }
           }catch (SQLException e) {
                        e.printStackTrace();			
           }
           finally{
                   try{
                       rs.close();
                       st.close();
                      }catch(Exception e){
                                  e.printStackTrace();
                                         }
                   }
      return datos;
    }
    
    public List<Reserva> getDatosEstOcupacion(String id, String fechaInicio, String fechaFin, String tipoEstadistica) throws Exception {                    
        
        String datoQuery = "";
        Statement st = null;
        ResultSet rs = null;  
        List<Reserva> datos = new ArrayList();
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy"); 
        
        if(tipoEstadistica.equals("cliente")){
            datoQuery = "userid";
        }else if(tipoEstadistica.equals("aula")){
            datoQuery = "spaceid";
        }
        
        try {
             st = conn.createStatement();
             String select = "";
               
             if(id != null && fechaInicio != null && fechaFin != null){
                
                 select = "select * from bookings where "+datoQuery+" = "+
                     id + " and record_date > '"+dateFormat.parse(fechaInicio)+"' and record_date < '"+dateFormat.parse(fechaFin)+"' order by "+datoQuery;
             
             }else if(id != null && fechaInicio == null && fechaFin == null){
                
                 select = "select * from bookings where "+datoQuery+" = "+id+ " order by "+datoQuery; 
             
             }else if(id != null && fechaInicio != null && fechaFin == null){
                
                 select = "select * from bookings where "+datoQuery+" = "+
                     id + " and record_date > '"+dateFormat.parse(fechaInicio)+"' order by "+datoQuery; 
             
             }else if(id != null && fechaInicio == null && fechaFin != null){
                
                 select = "select * from bookings where "+datoQuery+" = "+
                     id + " and record_date < '"+dateFormat.parse(fechaFin)+"' order by "+datoQuery; 
             
             }else if(id == null && fechaInicio != null && fechaFin == null){
                  
                select = "select * from bookings bo, users us where bo.userid = us.userid and us.roleid = 0 and bo.record_date > '"+dateFormat.parse(fechaInicio)+"' order by bo."+datoQuery; 
             
             }else if(id == null && fechaInicio != null && fechaFin != null){
               
                 select = "select * from bookings bo, users us where bo.userid = us.userid and us.roleid = 0 and bo.record_date > '"+dateFormat.parse(fechaInicio)+
                         "' and bo.record_date < '"+dateFormat.parse(fechaFin)+"' order by bo."+datoQuery; 
             
             }else if(id == null && fechaInicio == null && fechaFin != null){
               
                 select = "select * from bookings bo, users us where bo.userid = us.userid and us.roleid = 0 and "+
                         " bo.record_date < '"+dateFormat.parse(fechaFin)+"' order by bo."+datoQuery; 
             
             }else if(id == null && fechaInicio == null && fechaFin == null){
                
                 select = "select * from bookings bo "+
                         //, users us where bo.userid = us.userid and us.roleid = 0 "+
                         " order by bo."+datoQuery; 
             }              
                 System.out.println(select);   
             rs = st.executeQuery(select);
             while (rs.next()) {
                 Reserva usuario = new Reserva();  
                 usuario.setIdUsuario(rs.getString("userid"));
                 usuario.setIdAula( rs.getInt("spaceid"));
                 usuario.setNumeroOcupantesAula( rs.getInt("pax"));                 
                 usuario.setStatusReserva( rs.getString("status"));
                 usuario.setFechaPeticionInicioReserva(rs.getTimestamp("start_time"));                 
                 usuario.setFechaPeticionFinReserva(rs.getTimestamp("end_time"));
                   datos.add(usuario);
             }
           }catch (SQLException e) {
                        e.printStackTrace();			
           }
           finally{
                   try{
                       rs.close();
                       st.close();
                      }catch(Exception e){
                                  e.printStackTrace();
                                         }
                   }
      return datos;
    }

    /**
     * 
     * @return
     * @throws Exception 
     */ 
    public List<Aula> consultaAulas()throws Exception {
    	List<Aula> listaAulas = new ArrayList<Aula>();	
     	Statement st = null;
     	ResultSet rs = null;
     	try {
     		
                st = conn.createStatement();
     		String select = "SELECT spaceid,spacedescription,capacity,"
                        + "price_hour,price_day,url_map,"
                        + "how_to_get,postal_code,city,address,creation_date,creation_user,inactivation_date,"
                        + "inactivation_user,record_date"                   
                        + " FROM spaces ORDER BY capacity";			
                
     		rs = st.executeQuery(select);
     		while (rs.next()) {
     			Aula aula = new Aula();
                        aula.setId(rs.getInt("spaceid"));
                        aula.setDescripcion(rs.getString("spacedescription"));
                        aula.setCapacidad(rs.getInt("capacity"));
                        aula.setPrecioHora(rs.getDouble("price_hour"));
                        aula.setPrecioDia(rs.getDouble("price_day"));
                        aula.setUrl(rs.getString("url_map"));
                        aula.setIndicaciones(rs.getString("how_to_get"));
                        aula.setCodigoPostal(rs.getInt("postal_code"));
                        aula.setCiudad(rs.getString("city"));
                        aula.setDireccion(rs.getString("address"));
                        aula.setCreationDate(rs.getDate("creation_date"));
                        aula.setCreationUser(rs.getString("creation_user"));
                        aula.setInactivationDate(rs.getDate("inactivation_date"));
                        aula.setInactivationUser(rs.getString("inactivation_user"));
                        aula.setRecordDate(rs.getDate("record_date"));
     		        listaAulas.add(aula);
     		}
     	}catch (SQLException e) {
     		e.printStackTrace();			
     	}
     	finally{
     		try{
     			rs.close();
     			st.close();
     		}catch(Exception e){
     			e.printStackTrace();
     		}
     	}
     	return listaAulas;
    }
    
    /**
     * Consulta de Aula por identificador
     * @param pIDAula Identificador del Aula
     * @return objeto Aula con identificador igual al del parámetro
     * @exception EAulasReservaException
     */   
    public Aula consultaAulas(int pIdAula) throws Exception {
        Aula aula= null;

        Statement sentencia = conn.createStatement();
        String select = "SELECT * "
                + "FROM spaces WHERE spaceid = " + pIdAula;
        try {
            ResultSet rs = sentencia.executeQuery(select);
            if (rs.next()) {
                
            }
            rs.close();
            sentencia.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

        return aula;
    }   
    
  
    public Boolean altaAula(Aula oAula) throws Exception {    	
       	Date fechaActual = new Date();
       	SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd");
       	String cadenaFecha = formato.format(fechaActual);
       	Statement sentencia = conn.createStatement();
		String query = "INSERT INTO spaces( spacedescription, capacity, price_hour, price_day, url_map, how_to_get, postal_code, city, address, creation_date, creation_user)"+
		                            "VALUES ('"+oAula.getDescripcion()+"',"
		                                      +oAula.getCapacidad()+","
		                                      +oAula.getPrecioHora()+","
		                                      +oAula.getPrecioDia()+",'"
		                                      +oAula.getUrl()+"','"
		                                      +oAula.getIndicaciones()+"',"
		                                      +oAula.getCodigoPostal()+",'"
		                                      +oAula.getCiudad()+"','"
		                                      +oAula.getDireccion()+"','"
		                                      +fechaActual+"','"
		                                      +"53543567G'"
		                                      +")";
	
       	try {
       		sentencia.execute(query);

       	}
       	catch (SQLException e) {
    		e.printStackTrace();	
    		return Boolean.FALSE;
    	}
        return Boolean.TRUE;
    }   
     
    public List<Recurso> consultaExtras()throws Exception {
    	List<Recurso> listaRecursos = new ArrayList<Recurso>();	
     	Statement st = null;
     	ResultSet rs = null;
     	try {
                st = conn.createStatement();
     		String select = "SELECT resourceid,resourcedescription,price_day,price_hour"
                        + " FROM all_resources ORDER BY resourcedescription";			
     		rs = st.executeQuery(select);
     		while (rs.next()) {
     			Recurso recurso = new Recurso();
                        recurso.setId(rs.getInt("resourceid"));
                        recurso.setDescripcion(rs.getString("resourcedescription"));
                        recurso.setPrecioDia(rs.getDouble("price_day"));
                        recurso.setPrecioHora(rs.getDouble("price_hour"));
     		        listaRecursos.add(recurso);
     		}
     	}catch (SQLException e) {
     		e.printStackTrace();			
     	}
     	finally{
     		try{
     			rs.close();
     			st.close();
     		}catch(Exception e){
     			e.printStackTrace();
     		}
     	}
     	return listaRecursos;
    }

	public Integer dameUltimaReserva() throws Exception {
                Integer contador = 0;
                Statement st = null;
                ResultSet rs = null;
                try {
                    st = conn.createStatement();
                    
                    String select = "Select last_value from booking_seq";
                    rs = st.executeQuery(select);
                    
                    while (rs.next()) {
                        contador = rs.getInt("last_value");
                    }
                }catch (SQLException e) {
                        e.printStackTrace();			
                }
                finally{
                        try{
                            rs.close();
                            st.close();
                            }catch(Exception e){
                                e.printStackTrace();
                            }
                        }
                return contador;
	}	

     public Boolean setInsertReserva(Integer Id,String userid,Integer spaceid,Integer resourceId,String start_time,String end_time,Integer pax,String status,String creation_time,double importe) throws Exception {
        Boolean ok = false;
        Statement st = null;
        SimpleDateFormat sdf= new SimpleDateFormat("dd-MM-yyyy hh:mm"); 
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");        
        java.util.Date d1=sdf.parse(start_time);         
        java.util.Date d2=sdf.parse(end_time);     
                
        try {
            st = conn.createStatement();
            String insert = "INSERT INTO bookings (bookingid,userid,spaceid,start_time,end_time,pax,status,creation_time,record_date) values (" + 
                            "NEXTVAL('booking_seq')," +
                            "'" + userid + "'," +
                            "'" + spaceid + "'," +
                            "'" + d1 + "','" + d2 + "'," +
                            "'" + pax + "'," +
                            "'" + status + "'," +
                            "'" + dateFormat.parse(creation_time) + "'," +
                            "'" + dateFormat.parse(creation_time) + "')";
             System.out.println(insert);
             System.out.println(resourceId);
            
            
            log.escribe(TDSLanguageUtils.getMessage("log.insert"));
            
            st.executeUpdate(insert);            
            Id = Id + 1;
            
            if (resourceId > 0) {
                insert = "INSERT INTO booking_resources (bookingid,resourceId,record_date) values (" + 
                            "'" + Id + "'," +
                            "'" + resourceId + "'," +
                            "'" + dateFormat.parse(creation_time) + "')";
               
                System.out.println(insert);
                  log.escribe(TDSLanguageUtils.getMessage("log.insert"));
            
                  st.executeUpdate(insert);
            }
                    
            insert = "INSERT INTO payments (bookingid,payment_date,total_price,record_date) values (" + 
                            "'" + Id + "'," +
                         //   "'" + creation_time + "'," +
                            "'" + dateFormat.parse(creation_time) + "'," +
                            "'" + importe + "'," +
                            "'" + dateFormat.parse(creation_time) + "')";
               
                  log.escribe(TDSLanguageUtils.getMessage("log.insert"));
                System.out.println(insert);
            
                  st.executeUpdate(insert);
            
           
            ok = true;
            
            
            }catch (SQLException e) {
                        e.printStackTrace();			
                }
                finally{
                        try{
                            conn.commit();
                            st.close();
                            }catch(Exception e){
                                e.printStackTrace();
                            }
                        }        
        
        return ok;
        
    }                

	public List<ReservaRecurso> getRecurso(Integer Bookings) throws Exception {
                List<ReservaRecurso> listaResource = new ArrayList<ReservaRecurso>();	
                Statement st = null;
                ResultSet rs = null;
                try {
                    st = conn.createStatement();
                    
                    String select = "Select resourceid from booking_resources" +
                                    " Where bookingid = " + Bookings;
                    select = select + " Order by resourceid";  
                    rs = st.executeQuery(select);
                    
                    while (rs.next()) {
       			ReservaRecurso recurso = new ReservaRecurso();                        
                        recurso.setIdReserva(Bookings);
                        recurso.setIdRecurso(rs.getInt("resourceid"));
                        listaResource.add(recurso);
                    }
                }catch (SQLException e) {
                        e.printStackTrace();			
                }
                finally{
                        try{
                            rs.close();
                            st.close();
                            }catch(Exception e){
                                e.printStackTrace();
                            }
                        }
                return listaResource;
	}	
     
    public Recurso consultaRecurso(int IdRecurso) throws Exception {
        Recurso recurso = new Recurso();

        Statement sentencia = conn.createStatement();
        String select = "SELECT * "
                + "FROM all_resources WHERE resourceid = " + IdRecurso;
        try {
            ResultSet rs = sentencia.executeQuery(select);
            if (rs.next()) {
                recurso.setDescripcion(rs.getString("resourcedescription"));
                recurso.setPrecioDia(rs.getDouble("price_day"));
                recurso.setPrecioHora(rs.getDouble("price_hour"));
            }
            rs.close();
            sentencia.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

        return recurso;
    }
    
    public Recurso consultaExtra(int IdAula) throws Exception {
        Recurso recurso = new Recurso();

        Statement sentencia = conn.createStatement();
        String select = "select t2.resourceid,t2.resourcedescription,t2.amount,t2.price_hour,t2.price_day " +
                "from spaces_resources t1 " +
                "left join all_resources t2 on (t1.resourceid = t2.resourceid) " + 
                "where t1.spaceid = " + IdAula;
        try {
            ResultSet rs = sentencia.executeQuery(select);
            if (rs.next()) {
                recurso.setId(rs.getInt("resourceid"));
                recurso.setDescripcion(rs.getString("resourcedescription"));
                recurso.setPrecioDia(rs.getDouble("price_day"));
                recurso.setPrecioHora(rs.getDouble("price_hour"));
            }
            rs.close();
            sentencia.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return recurso;
    }   
    
    public List<Recurso> consultaRecursos(String filtro) throws Exception {
    	List<Recurso> listaRecurso = new LinkedList<Recurso>();

        Statement sentencia = conn.createStatement();
        String select = "SELECT * "
                + "FROM spaces_resources WHERE resource_description like '%" + filtro +"%'";
        try {
            ResultSet rs = sentencia.executeQuery(select);
            while (rs.next()) {
            	Recurso recurso = new Recurso();
                recurso.setDescripcion(rs.getString("resource_description"));
                recurso.setId(rs.getInt("spaceid"));
                listaRecurso.add(recurso);
            }
            rs.close();
            sentencia.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return listaRecurso;
    }

	public Boolean altaRecurso(Recurso recurso, String idCliente) {
		Statement sentencia;
		Date fechaActual = new Date();
       	SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd");
       	String cadenaFecha = formato.format(fechaActual);
		 try {
			 sentencia = conn.createStatement();
			 String insert = "INSERT INTO all_resources(resourcedescription, amount, price_hour, price_day, creation_date, creation_user) "
				           + "VALUES ('"+recurso.getDescripcion() +"',"+recurso.getCantidad()+","+recurso.getPrecioHora()+","+recurso.getPrecioDia()+",'"+cadenaFecha+"','"+idCliente+"')";
			 sentencia.executeUpdate(insert);
		} catch (SQLException exception) {
			exception.printStackTrace();
		}
		return true;
	}

	public Boolean bajaAula(Integer idAula, String idCliente) {
		Date fechaActual = new Date();
       	SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd");
       	String cadenaFecha = formato.format(fechaActual);
		Statement sentencia;
		 try {
			 sentencia = conn.createStatement();
			 String delete = "update spaces set inactivation_date='"+cadenaFecha+"', "+
			 				 "inactivation_user='"+idCliente+"'"+
			 				 " WHERE spaceid="+idAula;
			 sentencia.executeUpdate(delete);
		} catch (SQLException exception) {
			exception.printStackTrace();
		}
		return true;
	}   
       
	public List<Aula> BuscaAulas(String filtro) {
		List<Aula> listaAulas = new LinkedList<Aula>();
		Statement sentencia;
		 try {
			 sentencia = conn.createStatement();
			 String select = "select * from spaces where spacedescription like '%"+filtro+"%'";
			 ResultSet rs = sentencia.executeQuery(select);
	         while (rs.next()) {
	        	 Aula aula= new Aula();
	        	 aula.setId(rs.getInt("spaceid"));
	        	 aula.setDescripcion(rs.getString("spacedescription"));
	        	 aula.setCapacidad(rs.getInt("capacity"));
	        	 aula.setPrecioDia(rs.getDouble("price_day"));
	        	 aula.setPrecioHora(rs.getDouble("price_hour"));
	        	 aula.setUrl(rs.getString("url_map"));
	        	 aula.setIndicaciones(rs.getString("how_to_get"));
	        	 aula.setCodigoPostal(rs.getInt("postal_code"));
	        	 aula.setCiudad(rs.getString("city"));
	        	 aula.setDireccion(rs.getString("address"));
	        	 listaAulas.add(aula);
	         }
		} catch (SQLException exception) {
			exception.printStackTrace();
		}
		return listaAulas;
	}   
	
	public Boolean updateAula(Aula aula) {
		try {
			Statement sentencia = conn.createStatement();
			String update = "UPDATE SPACES SET "+
							"spacedescription='"+aula.getDescripcion().trim()+"', "+
							"capacity="+aula.getCapacidad()+", "+
							"price_hour="+aula.getPrecioHora()+", "+
							"price_day="+aula.getPrecioDia()+", "+
							"url_map='"+aula.getUrl().trim()+"', "+
							"how_to_get='"+aula.getIndicaciones().trim()+"', "+
							"postal_code="+aula.getCodigoPostal()+", "+
							"city='"+aula.getCiudad().trim()+"', "+
							"address='"+aula.getDireccion().trim()+"' "+
							"WHERE spaceid="+aula.getId();
			sentencia.executeUpdate(update);
		} catch (SQLException exception) {
			return Boolean.FALSE;
		}
		return Boolean.TRUE;
	} 
}
